﻿IF OBJECT_ID ('CHO_TASK_SCHEDULES') IS NOT NULL
	DROP TABLE CHO_TASK_SCHEDULES
GO

CREATE TABLE CHO_TASK_SCHEDULES
	(
	SCHEDULE_ID					INT NOT NULL,
	SCHEDULE_NAME				NVARCHAR(50) NOT NULL,
	SCHEDULE_DESC				NVARCHAR(2500),
	TASK_ID	  					INT NOT NULL,
	[ENABLED]					BIT NOT NULL  DEFAULT(1),
	[START_DATE]				DATETIME NOT NULL,
	[END_DATE]					DATETIME,
	START_TIME					DATETIME NOT NULL,
	END_TIME					DATETIME,
	HOLIDAY_CAL_ID				INT,
	SCHEDULE_TYPE_ID			INT,
	T1							INT,
	DAILY_EVERY_DAYS			INT,
	WEEKLY_EVERY_WEEKS			INT,
	SUN							BIT NOT NULL DEFAULT(0),
	MON							BIT NOT NULL DEFAULT(0),
	TUE							BIT NOT NULL DEFAULT(0),
	WED							BIT NOT NULL DEFAULT(0),
	THU							BIT NOT NULL DEFAULT(0),
	FRI							BIT NOT NULL DEFAULT(0),
	SAT							BIT NOT NULL DEFAULT(0),
	MONTHLY_DAY_OF_MONTH		BIT,
	MONTHLY_EVERY_DAY_OF_MONTH	INT,
	MONTHLY_EVERY_WEEK_OF_MONTH	INT,
	MONTHLY_EVERY_WEEKDAY_OF_MONTH	INT,
	MONTHLY_SELECTED_MONTHS		CHAR(12),
	REPEAT_ENABLED				BIT NOT NULL DEFAULT(0),
	REPEAT_INTERVAL				INT,
	REPEAT_INTERVAL_TYPE		INT,
	DEPENDENCIES				NVARCHAR(2500),
	FIRE_AFTER_DEPENDENCY		INT NOT NULL DEFAULT(0),
	CREATED_BY					NVARCHAR(100) NOT NULL,
	CREATED_DATE				DATETIME NOT NULL,
	MODIFIED_BY					NVARCHAR(100) NOT NULL,
	MODIFIED_DATE				DATETIME NOT NULL,
	CONSTRAINT PK_CHO_TASK_SCHEDULES PRIMARY KEY (SCHEDULE_ID),
    CONSTRAINT [UQ_CHO_TASK_SCHEDULES] UNIQUE (SCHEDULE_NAME, TASK_ID)
	)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_TASK_SCHEDULES_AUDIT') IS NOT NULL
	DROP TABLE CHO_TASK_SCHEDULES_AUDIT
GO

CREATE TABLE CHO_TASK_SCHEDULES_AUDIT
	(
	SCHEDULE_ID					INT NOT NULL,
	SCHEDULE_NAME				NVARCHAR(50) NOT NULL,
	SCHEDULE_DESC				NVARCHAR(2500),
	TASK_ID	  					INT NOT NULL,
	[ENABLED]					BIT NOT NULL  DEFAULT(1),
	[START_DATE]				DATETIME NOT NULL,
	[END_DATE]					DATETIME,
	START_TIME					DATETIME NOT NULL,
	END_TIME					DATETIME,
	HOLIDAY_CAL_ID				INT,
	SCHEDULE_TYPE_ID			INT,
	T1							INT,
	DAILY_EVERY_DAYS			INT,
	WEEKLY_EVERY_WEEKS			INT,
	SUN							BIT NOT NULL DEFAULT(0),
	MON							BIT NOT NULL DEFAULT(0),
	TUE							BIT NOT NULL DEFAULT(0),
	WED							BIT NOT NULL DEFAULT(0),
	THU							BIT NOT NULL DEFAULT(0),
	FRI							BIT NOT NULL DEFAULT(0),
	SAT							BIT NOT NULL DEFAULT(0),
	MONTHLY_DAY_OF_MONTH		BIT,
	MONTHLY_EVERY_DAY_OF_MONTH	INT,
	MONTHLY_EVERY_WEEK_OF_MONTH	INT,
	MONTHLY_EVERY_WEEKDAY_OF_MONTH	INT,
	MONTHLY_SELECTED_MONTHS		CHAR(12),
	REPEAT_ENABLED				BIT NOT NULL DEFAULT(0),
	REPEAT_INTERVAL				INT,
	REPEAT_INTERVAL_TYPE		INT,
	DEPENDENCIES				NVARCHAR(2500),
	FIRE_AFTER_DEPENDENCY		INT NOT NULL DEFAULT(0),
	CREATED_BY					NVARCHAR(100) NOT NULL,
	CREATED_DATE				DATETIME NOT NULL,
	MODIFIED_BY					NVARCHAR(100) NOT NULL,
	MODIFIED_DATE				DATETIME NOT NULL,
	CONSTRAINT PK_CHO_TASK_SCHEDULES_AUDIT PRIMARY KEY (SCHEDULE_ID, MODIFIED_DATE)
	)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_ADD_OR_UPDATE_TASK_SCHEDULES') IS NOT NULL
	DROP PROC CHO_ADD_OR_UPDATE_TASK_SCHEDULES
GO

CREATE PROCEDURE CHO_ADD_OR_UPDATE_TASK_SCHEDULES
		@SCHEDULE_NAME NVARCHAR(50),
		@SCHEDULE_DESC NVARCHAR(2500),
		@TASK_ID INT,
		@ENABLED BIT,
		@START_DATE DATETIME,
		@END_DATE DATETIME,
		@START_TIME	DATETIME,
		@END_TIME DATETIME,
		@HOLIDAY_CAL_ID INT,
		@SCHEDULE_TYPE_ID INT,
		@T1 INT,
		@DAILY_EVERY_DAYS INT,
		@WEEKLY_EVERY_WEEKS INT,
		@SUN BIT,
		@MON BIT,
		@TUE BIT,
		@WED BIT,
		@THU BIT,
		@FRI BIT,
		@SAT BIT,
		@MONTHLY_DAY_OF_MONTH BIT,
		@MONTHLY_EVERY_DAY_OF_MONTH INT,
		@MONTHLY_EVERY_WEEK_OF_MONTH INT,
		@MONTHLY_EVERY_WEEKDAY_OF_MONTH	INT,
		@MONTHLY_SELECTED_MONTHS CHAR(12),
		@REPEAT_ENABLED BIT,
		@REPEAT_INTERVAL INT,
		@REPEAT_INTERVAL_TYPE INT,
		@DEPENDENCIES NVARCHAR(2500),
		@FIRE_AFTER_DEPENDENCY	INT,
		@MODIFIED_BY NVARCHAR(100),
		@SCHEDULE_ID INT OUTPUT
		AS

	SET NOCOUNT ON

	SELECT @SCHEDULE_ID = NULL
	DECLARE @MODIFIED_DATE DATETIME
	DECLARE @ERR_MESSAGE NVARCHAR(255)
	DECLARE @TASK_NAME NVARCHAR(50)

	IF NOT EXISTS (SELECT 1 FROM CHO_TASKS WHERE @TASK_ID = TASK_ID AND DELETED = 0)
	BEGIN
		SET @ERR_MESSAGE = 'Task not exists/deleted.'
		RAISERROR (@ERR_MESSAGE, 11,1)
		RETURN
	END	
	
	SELECT @MODIFIED_DATE = GETDATE()
	
	SELECT @SCHEDULE_ID = SCHEDULE_ID FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_NAME = @SCHEDULE_NAME AND TASK_ID = @TASK_ID

	IF @SCHEDULE_ID is NULL
	BEGIN
		INSERT CHO_TASK_SCHEDULES (SCHEDULE_NAME, SCHEDULE_DESC, TASK_ID, [ENABLED], [START_DATE], [END_DATE], 
			START_TIME, END_TIME, HOLIDAY_CAL_ID, SCHEDULE_TYPE_ID, T1, DAILY_EVERY_DAYS, WEEKLY_EVERY_WEEKS, 
			SUN, MON, TUE, WED, THU, FRI, SAT, MONTHLY_DAY_OF_MONTH,
			MONTHLY_EVERY_DAY_OF_MONTH, 
			MONTHLY_EVERY_WEEK_OF_MONTH,
			MONTHLY_EVERY_WEEKDAY_OF_MONTH,
			MONTHLY_SELECTED_MONTHS,
			REPEAT_ENABLED,
			REPEAT_INTERVAL,
			REPEAT_INTERVAL_TYPE,
			DEPENDENCIES,
			FIRE_AFTER_DEPENDENCY,
			CREATED_BY, CREATED_DATE,
			MODIFIED_BY, MODIFIED_DATE)
		VALUES (@SCHEDULE_NAME, @SCHEDULE_DESC, @TASK_ID, @ENABLED, @START_DATE, @END_DATE, 
			@START_TIME, @END_TIME, @HOLIDAY_CAL_ID, @SCHEDULE_TYPE_ID, @T1, @DAILY_EVERY_DAYS, @WEEKLY_EVERY_WEEKS, 
			@SUN, @MON, @TUE, @WED, @THU, @FRI, @SAT, @MONTHLY_DAY_OF_MONTH,
			@MONTHLY_EVERY_DAY_OF_MONTH, 
			@MONTHLY_EVERY_WEEK_OF_MONTH,
			@MONTHLY_EVERY_WEEKDAY_OF_MONTH,
			@MONTHLY_SELECTED_MONTHS,
			@REPEAT_ENABLED,
			@REPEAT_INTERVAL,
			@REPEAT_INTERVAL_TYPE,
			@DEPENDENCIES,
			@FIRE_AFTER_DEPENDENCY,
			@MODIFIED_BY, @MODIFIED_DATE,
			@MODIFIED_BY, @MODIFIED_DATE)
		SELECT @SCHEDULE_ID = @@IDENTITY
	END
	ELSE
	BEGIN
		IF (@SCHEDULE_NAME is NULL)
			SELECT @SCHEDULE_NAME = SCHEDULE_NAME FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@SCHEDULE_DESC is NULL)
			SELECT @SCHEDULE_DESC = SCHEDULE_DESC FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@ENABLED is NULL)
			SELECT @ENABLED = [ENABLED] FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@START_DATE is NULL)
			SELECT @START_DATE = [START_DATE] FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@END_DATE is NULL)
			SELECT @END_DATE = END_DATE FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@START_TIME is NULL)
			SELECT @START_TIME = START_TIME FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@END_TIME is NULL)
			SELECT @END_TIME = END_TIME FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@SUN is NULL)
			SELECT @SUN = SUN FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@HOLIDAY_CAL_ID is NULL)
			SELECT @HOLIDAY_CAL_ID = HOLIDAY_CAL_ID FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@SCHEDULE_TYPE_ID is NULL)
			SELECT @SCHEDULE_TYPE_ID = SCHEDULE_TYPE_ID FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@T1 is NULL)
			SELECT @T1 = T1 FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@DAILY_EVERY_DAYS is NULL)
			SELECT @DAILY_EVERY_DAYS = DAILY_EVERY_DAYS FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@WEEKLY_EVERY_WEEKS is NULL)
			SELECT @WEEKLY_EVERY_WEEKS = WEEKLY_EVERY_WEEKS FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@MON is NULL)
			SELECT @MON = MON FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@TUE is NULL)
			SELECT @TUE = TUE FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@WED is NULL)
			SELECT @WED = WED FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@THU is NULL)
			SELECT @THU = THU FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@FRI is NULL)
			SELECT @FRI = FRI FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@SAT is NULL)
			SELECT @SAT = SAT FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@MONTHLY_DAY_OF_MONTH is NULL)
			SELECT @MONTHLY_DAY_OF_MONTH = MONTHLY_DAY_OF_MONTH FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@MONTHLY_EVERY_DAY_OF_MONTH is NULL)
			SELECT MONTHLY_EVERY_DAY_OF_MONTH = @MONTHLY_EVERY_DAY_OF_MONTH FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@MONTHLY_EVERY_WEEK_OF_MONTH is NULL)
			SELECT MONTHLY_EVERY_WEEK_OF_MONTH = @MONTHLY_EVERY_WEEK_OF_MONTH FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@MONTHLY_EVERY_WEEKDAY_OF_MONTH is NULL)
			SELECT MONTHLY_EVERY_WEEKDAY_OF_MONTH = @MONTHLY_EVERY_WEEKDAY_OF_MONTH FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@MONTHLY_SELECTED_MONTHS is NULL)
			SELECT MONTHLY_SELECTED_MONTHS = @MONTHLY_SELECTED_MONTHS FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@REPEAT_ENABLED is NULL)
			SELECT REPEAT_ENABLED = @REPEAT_ENABLED FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@REPEAT_INTERVAL is NULL)
			SELECT REPEAT_INTERVAL = @REPEAT_INTERVAL FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@REPEAT_INTERVAL_TYPE is NULL)
			SELECT REPEAT_INTERVAL_TYPE = @REPEAT_INTERVAL_TYPE FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
		IF (@DEPENDENCIES is NULL)
			SELECT DEPENDENCIES = @DEPENDENCIES FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID

		UPDATE CHO_TASK_SCHEDULES
			SET 
				SCHEDULE_NAME = @SCHEDULE_NAME,
				SCHEDULE_DESC = @SCHEDULE_DESC,
				TASK_ID = @TASK_ID,
				[ENABLED] = @ENABLED,
				START_DATE = @START_DATE,
				END_DATE = @END_DATE,
				START_TIME = @START_TIME,
				END_TIME = @END_TIME,
				HOLIDAY_CAL_ID = @HOLIDAY_CAL_ID,
				SCHEDULE_TYPE_ID = @SCHEDULE_TYPE_ID,
				T1 = @T1,
				DAILY_EVERY_DAYS = @DAILY_EVERY_DAYS,
				WEEKLY_EVERY_WEEKS = @WEEKLY_EVERY_WEEKS,
				SUN = @SUN,
				MON = @MON,
				TUE = @TUE,
				WED = @WED,
				THU = @THU,
				FRI = @FRI,
				SAT = @SAT,
				MONTHLY_DAY_OF_MONTH = @MONTHLY_DAY_OF_MONTH,
				MONTHLY_EVERY_DAY_OF_MONTH = @MONTHLY_EVERY_DAY_OF_MONTH,
				MONTHLY_EVERY_WEEK_OF_MONTH = @MONTHLY_EVERY_WEEK_OF_MONTH,
				MONTHLY_EVERY_WEEKDAY_OF_MONTH = @MONTHLY_EVERY_WEEKDAY_OF_MONTH,
				MONTHLY_SELECTED_MONTHS = @MONTHLY_SELECTED_MONTHS,
				REPEAT_ENABLED = @REPEAT_ENABLED,
				REPEAT_INTERVAL = @REPEAT_INTERVAL,
				REPEAT_INTERVAL_TYPE = @REPEAT_INTERVAL_TYPE,
				DEPENDENCIES = @DEPENDENCIES,
				FIRE_AFTER_DEPENDENCY = @FIRE_AFTER_DEPENDENCY,
				MODIFIED_BY = @MODIFIED_BY,
				MODIFIED_DATE = @MODIFIED_DATE
			WHERE
				SCHEDULE_ID = @SCHEDULE_ID
	END
		
	INSERT INTO CHO_TASK_SCHEDULES_AUDIT 
		SELECT * FROM CHO_TASK_SCHEDULES
		WHERE 
			SCHEDULE_ID = @SCHEDULE_ID
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_DELETE_TASK_SCHEDULES') IS NOT NULL
	DROP PROC CHO_DELETE_TASK_SCHEDULES
GO

CREATE PROCEDURE CHO_DELETE_TASK_SCHEDULES
		@SCHEDULE_NAME NVARCHAR(50),
		@MODIFIED_BY NVARCHAR(100),
		@SCHEDULE_ID INT OUTPUT
		AS

	SET NOCOUNT ON

	SELECT @SCHEDULE_ID = NULL

	SELECT @SCHEDULE_ID = SCHEDULE_ID FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_NAME = @SCHEDULE_NAME
	IF @SCHEDULE_ID is NULL
	BEGIN
		SELECT @SCHEDULE_ID = 0
	END
	ELSE
	BEGIN
		UPDATE CHO_TASK_SCHEDULES SET MODIFIED_BY = @MODIFIED_BY, MODIFIED_DATE = GETDATE() WHERE SCHEDULE_ID = @SCHEDULE_ID

		INSERT INTO CHO_TASK_SCHEDULES_AUDIT 
			SELECT * FROM CHO_TASK_SCHEDULES
			WHERE
				SCHEDULE_ID = @SCHEDULE_ID

		DELETE FROM CHO_TASK_SCHEDULES WHERE SCHEDULE_ID = @SCHEDULE_ID
	END
GO
----------------------------------------------------------------

IF OBJECT_ID ('CHO_DELETE_ALL_TASK_SCHEDULES') IS NOT NULL
	DROP PROC CHO_DELETE_ALL_TASK_SCHEDULES
GO

CREATE PROCEDURE CHO_DELETE_ALL_TASK_SCHEDULES
		@TASK_ID INT,
		@MODIFIED_BY NVARCHAR(100)
		AS

	SET NOCOUNT ON

	UPDATE CHO_TASK_SCHEDULES SET MODIFIED_BY = @MODIFIED_BY, MODIFIED_DATE = GETDATE() WHERE TASK_ID = @TASK_ID

	INSERT INTO CHO_TASK_SCHEDULES_AUDIT 
		SELECT * FROM CHO_TASK_SCHEDULES
		WHERE
			TASK_ID = @TASK_ID

	DELETE FROM CHO_TASK_SCHEDULES WHERE TASK_ID = @TASK_ID
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_SELECT_TASK_SCHEDULES') IS NOT NULL
	DROP PROC CHO_SELECT_TASK_SCHEDULES
GO

CREATE PROCEDURE CHO_SELECT_TASK_SCHEDULES
		@TASK_ID INT,
		@SCHEDULE_NAME NVARCHAR(50)
		AS

	SET NOCOUNT ON

	IF (@SCHEDULE_NAME IS NULL)
	BEGIN
		SELECT
			SCHEDULE_ID,
			SCHEDULE_NAME,
			SCHEDULE_DESC,
			TASK_ID,
			[ENABLED],
			[START_DATE],
			[END_DATE],
			START_TIME,
			END_TIME,
			HOLIDAY_CAL_ID,
			C.CAL_NAME as HOLIDAY_CAL_NAME,
			SCHEDULE_TYPE_ID,
			T1,
			DAILY_EVERY_DAYS,
			WEEKLY_EVERY_WEEKS,
			SUN,
			MON,
			TUE,
			WED,
			THU,
			FRI,
			SAT,
			MONTHLY_DAY_OF_MONTH,
			MONTHLY_EVERY_DAY_OF_MONTH,
			MONTHLY_EVERY_WEEK_OF_MONTH,
			MONTHLY_EVERY_WEEKDAY_OF_MONTH,
			MONTHLY_SELECTED_MONTHS,
			REPEAT_ENABLED,
			REPEAT_INTERVAL,
			REPEAT_INTERVAL_TYPE,
			DEPENDENCIES,
			FIRE_AFTER_DEPENDENCY,
			T.CREATED_BY, T.CREATED_DATE, T.MODIFIED_BY, T.MODIFIED_DATE
		FROM 
			CHO_TASK_SCHEDULES T,
			CHO_HOLIDAY_CALENDAR C
		WHERE 
			TASK_ID = @TASK_ID
			AND C.CAL_ID = T.HOLIDAY_CAL_ID
	END
	ELSE
	BEGIN
		SELECT
			SCHEDULE_ID,
			SCHEDULE_NAME,
			SCHEDULE_DESC,
			TASK_ID,
			[ENABLED],
			[START_DATE],
			[END_DATE],
			START_TIME,
			END_TIME,
			HOLIDAY_CAL_ID,
			C.CAL_NAME as HOLIDAY_CAL_NAME,
			SCHEDULE_TYPE_ID,
			T1,
			DAILY_EVERY_DAYS,
			WEEKLY_EVERY_WEEKS,
			SUN,
			MON,
			TUE,
			WED,
			THU,
			FRI,
			SAT,
			MONTHLY_DAY_OF_MONTH,
			MONTHLY_EVERY_DAY_OF_MONTH,
			MONTHLY_EVERY_WEEK_OF_MONTH,
			MONTHLY_EVERY_WEEKDAY_OF_MONTH,
			MONTHLY_SELECTED_MONTHS,
			REPEAT_ENABLED,
			REPEAT_INTERVAL,
			REPEAT_INTERVAL_TYPE,
			DEPENDENCIES,
			FIRE_AFTER_DEPENDENCY,
			T.CREATED_BY, T.CREATED_DATE, T.MODIFIED_BY, T.MODIFIED_DATE
		FROM 
			CHO_TASK_SCHEDULES T,
			CHO_HOLIDAY_CALENDAR C
		WHERE 
			TASK_ID = @TASK_ID
			AND C.CAL_ID = T.HOLIDAY_CAL_ID
			AND SCHEDULE_NAME = @SCHEDULE_NAME
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_CLONE_TASK_SCHEDULE_DEFINITION') IS NOT NULL
	DROP PROC CHO_CLONE_TASK_SCHEDULE_DEFINITION
GO

CREATE PROCEDURE CHO_CLONE_TASK_SCHEDULE_DEFINITION
		@TASK_ID INT,
		@NEW_TASK_ID INT,
		@MODIFIED_BY NVARCHAR(100)
		AS

	SET NOCOUNT ON

	DECLARE @ERR_MESSAGE NVARCHAR(255)
	DECLARE @SCHEDULE_ID INT
	DECLARE @SCHEDULE_NAME NVARCHAR(50)
	DECLARE @SCHEDULE_DESC NVARCHAR(2500)
	DECLARE @ENABLED BIT
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME
	DECLARE @START_TIME	DATETIME
	DECLARE @END_TIME DATETIME
	DECLARE @HOLIDAY_CAL_ID INT
	DECLARE @SCHEDULE_TYPE_ID INT
	DECLARE @T1 INT
	DECLARE @DAILY_EVERY_DAYS INT
	DECLARE @WEEKLY_EVERY_WEEKS INT
	DECLARE @SUN BIT
	DECLARE @MON BIT
	DECLARE @TUE BIT
	DECLARE @WED BIT
	DECLARE @THU BIT
	DECLARE @FRI BIT
	DECLARE @SAT BIT
	DECLARE @MONTHLY_DAY_OF_MONTH BIT
	DECLARE @MONTHLY_EVERY_DAY_OF_MONTH INT
	DECLARE @MONTHLY_EVERY_WEEK_OF_MONTH INT
	DECLARE @MONTHLY_EVERY_WEEKDAY_OF_MONTH INT
	DECLARE @MONTHLY_SELECTED_MONTHS CHAR(12)
	DECLARE @REPEAT_ENABLED BIT
	DECLARE @REPEAT_INTERVAL INT
	DECLARE @REPEAT_INTERVAL_TYPE INT
	DECLARE @DEPENDENCIES varchar(2500)
	DECLARE	@FIRE_AFTER_DEPENDENCY INT

	DECLARE SCHEDULE_CURSOR CURSOR FOR
		SELECT
			SCHEDULE_NAME,
			SCHEDULE_DESC,
			TASK_ID,
			[ENABLED],
			[START_DATE],
			END_DATE,
			START_TIME,
			END_TIME,
			HOLIDAY_CAL_ID,
			SCHEDULE_TYPE_ID,
			T1,
			DAILY_EVERY_DAYS,
			WEEKLY_EVERY_WEEKS,
			SUN,
			MON,
			TUE,
			WED,
			THU,
			FRI,
			SAT,
			MONTHLY_DAY_OF_MONTH,
			MONTHLY_EVERY_DAY_OF_MONTH,
			MONTHLY_EVERY_WEEK_OF_MONTH,
			MONTHLY_EVERY_WEEKDAY_OF_MONTH,
			MONTHLY_SELECTED_MONTHS,
			REPEAT_ENABLED,
			REPEAT_INTERVAL,
			REPEAT_INTERVAL_TYPE,
			DEPENDENCIES,
			FIRE_AFTER_DEPENDENCY
		FROM CHO_TASK_SCHEDULES WHERE TASK_ID = @TASK_ID;

	OPEN SCHEDULE_CURSOR;
	FETCH NEXT FROM SCHEDULE_CURSOR
		INTO
			@SCHEDULE_NAME,
			@SCHEDULE_DESC,
			@TASK_ID,
			@ENABLED,
			@START_DATE,
			@END_DATE,
			@START_TIME,
			@END_TIME,
			@HOLIDAY_CAL_ID,
			@SCHEDULE_TYPE_ID,
			@T1,
			@DAILY_EVERY_DAYS,
			@WEEKLY_EVERY_WEEKS,
			@SUN,
			@MON,
			@TUE,
			@WED,
			@THU,
			@FRI,
			@SAT,
			@MONTHLY_DAY_OF_MONTH,
			@MONTHLY_EVERY_DAY_OF_MONTH,
			@MONTHLY_EVERY_WEEK_OF_MONTH,
			@MONTHLY_EVERY_WEEKDAY_OF_MONTH,
			@MONTHLY_SELECTED_MONTHS,
			@REPEAT_ENABLED,
			@REPEAT_INTERVAL,
			@REPEAT_INTERVAL_TYPE,
			@DEPENDENCIES,
			@FIRE_AFTER_DEPENDENCY
		;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC CHO_ADD_OR_UPDATE_TASK_SCHEDULES 
				@SCHEDULE_NAME,
				@SCHEDULE_DESC,
				@NEW_TASK_ID,
				@ENABLED,
				@START_DATE,
				@END_DATE,
				@START_TIME,
				@END_TIME,
				@HOLIDAY_CAL_ID,
				@SCHEDULE_TYPE_ID,
				@T1,
				@DAILY_EVERY_DAYS,
				@WEEKLY_EVERY_WEEKS,
				@SUN,
				@MON,
				@TUE,
				@WED,
				@THU,
				@FRI,
				@SAT,
				@MONTHLY_DAY_OF_MONTH,
				@MONTHLY_EVERY_DAY_OF_MONTH,
				@MONTHLY_EVERY_WEEK_OF_MONTH,
				@MONTHLY_EVERY_WEEKDAY_OF_MONTH,
				@MONTHLY_SELECTED_MONTHS,
				@REPEAT_ENABLED,
				@REPEAT_INTERVAL,
				@REPEAT_INTERVAL_TYPE,
				@DEPENDENCIES,
				@FIRE_AFTER_DEPENDENCY,
				@MODIFIED_BY,
				@SCHEDULE_ID OUT

		FETCH NEXT FROM SCHEDULE_CURSOR
			INTO
				@SCHEDULE_NAME,
				@SCHEDULE_DESC,
				@TASK_ID,
				@ENABLED,
				@START_DATE,
				@END_DATE,
				@START_TIME,
				@END_TIME,
				@HOLIDAY_CAL_ID,
				@SCHEDULE_TYPE_ID,
				@T1,
				@DAILY_EVERY_DAYS,
				@WEEKLY_EVERY_WEEKS,
				@SUN,
				@MON,
				@TUE,
				@WED,
				@THU,
				@FRI,
				@SAT,
				@MONTHLY_DAY_OF_MONTH,
				@MONTHLY_EVERY_DAY_OF_MONTH,
				@MONTHLY_EVERY_WEEK_OF_MONTH,
				@MONTHLY_EVERY_WEEKDAY_OF_MONTH,
				@MONTHLY_SELECTED_MONTHS,
				@REPEAT_ENABLED,
				@REPEAT_INTERVAL,
				@REPEAT_INTERVAL_TYPE,
				@DEPENDENCIES,
				@FIRE_AFTER_DEPENDENCY
			;
	END;
	CLOSE SCHEDULE_CURSOR;
	DEALLOCATE SCHEDULE_CURSOR;
GO
----------------------------------------------------------------

DECLARE @SCHEDULE_ID INT
EXEC CHO_ADD_OR_UPDATE_TASK_SCHEDULES 'CHUBB_EOD1', NULL, 1, 1, '01/01/2013', NULL, '04:20PM', NULL, 1, 1, -1, 5, 2, 0, 1, 1, 0, 0, 0, 0, 1, 3, 1, 2, NULL, 0, NULL, NULL, NULL, 0, 'XBBKP28', @SCHEDULE_ID OUT
SELECT "@SCHEDULE_ID" = @SCHEDULE_ID
GO

DECLARE @SCHEDULE_ID INT
EXEC CHO_ADD_OR_UPDATE_TASK_SCHEDULES 'CHUBB_EOD2', NULL, 1, 1, '01/01/2013', NULL, '04:20PM', NULL, 2, 1, 2, 1, 4, 0, 1, 1, 0, 0, 0, 0, 0, 2, 3, 5, NULL, 0, NULL, NULL, NULL, 0, 'XBBKP28', @SCHEDULE_ID OUT
SELECT "@SCHEDULE_ID" = @SCHEDULE_ID
GO

--EXEC CHO_ADD_OR_UPDATE_TASK_BATCH 101, 'CHUBB_EOD1', 4, NULL, 'Starting batch...', '01/01/13', NULL, NULL, 0, 'XNYC123004454', 'AMS\RAJ'
--GO

--DECLARE @NEW_TASK_ID INT
--EXECUTE CHO_CLONE_TASK_DEFINITION 'CHUBB_EOD1', 'C10', 'CHUBB', 'Raj', @NEW_TASK_ID OUT
--SELECT "@NEW_TASK_ID" = @NEW_TASK_ID
